﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class ContestsDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoContests model)
        {
            model.CON_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Contests]");
            sql.Append("([CON_ID],[CON_COMPETITIONS],[CON_CONTENTS],[CON_YEAR],[CON_MONTH],[CON_ADDRESS],[CON_TEACHERS],[CON_NAME],[CON_STARTTIME],[CON_ENDTIME],[CON_CREATOR],[CON_EXPERIENCE],[CON_IMPROVE],[CON_SUMMARY],[CON_ISCHECK],[CON_ISSHOW])");
            sql.Append("VALUES");
            sql.Append("(@CON_ID,@CON_COMPETITIONS,@CON_CONTENTS,@CON_YEAR,@CON_MONTH,@CON_ADDRESS,@CON_TEACHERS,@CON_NAME,@CON_STARTTIME,@CON_ENDTIME,@CON_CREATOR,@CON_EXPERIENCE,@CON_IMPROVE,@CON_SUMMARY,@CON_ISCHECK,@CON_ISSHOW)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoContests model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Contests] SET ");
            sql.Append("CON_COMPETITIONS=@CON_COMPETITIONS");
            sql.Append(",CON_YEAR=@CON_YEAR");
            sql.Append(",CON_MONTH=@CON_MONTH");
            sql.Append(",CON_ADDRESS=@CON_ADDRESS");
            sql.Append(",CON_TEACHERS=@CON_TEACHERS");
            sql.Append(",CON_NAME=@CON_NAME");
            sql.Append(",CON_STARTTIME=@CON_STARTTIME");
            sql.Append(",CON_ENDTIME=@CON_ENDTIME");
            sql.Append(",CON_CREATOR=@CON_CREATOR");
            sql.Append(",CON_EXPERIENCE=@CON_EXPERIENCE");
            sql.Append(",CON_IMPROVE=@CON_IMPROVE");
            sql.Append(",CON_SUMMARY=@CON_SUMMARY");
            sql.Append(",CON_ISCHECK=@CON_ISCHECK");
            sql.Append(",CON_ISSHOW=@CON_ISSHOW");
            sql.Append(",CON_CONTENTS=@CON_CONTENTS");
            sql.Append(" WHERE ");
            sql.Append("CON_ID=@CON_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string cON_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Contests] SET ");
            sql.Append("CON_ISSHOW=-1");
            sql.Append(" WHERE ");
            sql.Append("CON_ID=@CON_ID ");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CON_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = cON_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
        /// <summary>
        /// 通过竞赛项目id来删除竞赛任务表
        /// </summary>
        /// <param name="competitionId"></param>
        /// <returns>返回受影响的行数</returns>
        public int DeleteContestByCompetitionId(string competitionId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Contests] SET");
            sql.Append(" CON_IsShow=-1");
            sql.Append(" WHERE");
            sql.Append(" CON_COMPETITIONS=@CON_COMPETITIONS");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CON_COMPETITIONS", SqlDbType.NVarChar, 50);
            parameter[0].Value = competitionId;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);

        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoContests GetModel(string cON_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CON_ID,CON_COMPETITIONS,CON_CONTENTS,CON_YEAR,CON_MONTH,CON_ADDRESS,CON_TEACHERS,CON_NAME,CON_STARTTIME,CON_ENDTIME,CON_CREATOR,CON_EXPERIENCE,CON_IMPROVE,CON_SUMMARY,CON_ISCHECK,CON_ISSHOW ");
            sql.Append(" ,A.ACA_NAME AS ACA_NAME, T1.TEA_NAME AS TEA_NAME, T2.TEA_NAME AS MANAGER_NAME ");
            sql.Append(" ,M.COM_HOST, M.COM_UNDERTAKE, M.COM_LEVEL, M.COM_CAPITAL, M.COM_CONTENTS ");
            sql.Append(" ,(SELECT MIN(P.PRO_STARTTIME) FROM [ContestDB].[dbo].[Procedures] P ");
            sql.Append(" INNER JOIN  [ContestDB].[dbo].[Phases] H ON P.PRO_PHASES=H.PHA_ID ");
            sql.Append(" WHERE P.PRO_CONTESTS=N.CON_ID AND H.PHA_NAME LIKE '%报名%' ) ENROLL_TIME ");
            sql.Append(" FROM Contests AS N INNER JOIN Competitions AS M ON N.CON_COMPETITIONS=M.COM_ID ");
            sql.Append(" LEFT JOIN Academy AS A ON M.COM_ACADEMY=A.ACA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T1 ON N.CON_CREATOR=T1.TEA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T2 ON M.COM_MANAGER=T2.TEA_ID ");
            sql.Append(" WHERE CON_ID=@CON_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@CON_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = cON_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoContests> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoContests> GetAllContests()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CON_ID,CON_COMPETITIONS,CON_CONTENTS,CON_YEAR,CON_MONTH,CON_ADDRESS,CON_TEACHERS,CON_NAME,CON_STARTTIME,CON_ENDTIME,CON_CREATOR,CON_EXPERIENCE,CON_IMPROVE,CON_SUMMARY,CON_ISCHECK,CON_ISSHOW ");
            sql.Append(" ,A.ACA_NAME AS ACA_NAME, T1.TEA_NAME AS TEA_NAME, T2.TEA_NAME AS MANAGER_NAME ");
            sql.Append(" ,M.COM_HOST, M.COM_UNDERTAKE, M.COM_LEVEL, M.COM_CAPITAL, M.COM_CONTENTS ");
            sql.Append(" ,(SELECT MIN(P.PRO_STARTTIME) FROM [ContestDB].[dbo].[Procedures] P ");
            sql.Append(" INNER JOIN  [ContestDB].[dbo].[Phases] H ON P.PRO_PHASES=H.PHA_ID ");
            sql.Append(" WHERE P.PRO_CONTESTS=N.CON_ID AND H.PHA_NAME LIKE '%报名%' ) ENROLL_TIME ");
            sql.Append(" FROM Contests AS N INNER JOIN Competitions AS M ON N.CON_COMPETITIONS=M.COM_ID ");
            sql.Append(" LEFT JOIN Academy AS A ON M.COM_ACADEMY=A.ACA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T1 ON N.CON_CREATOR=T1.TEA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T2 ON M.COM_MANAGER=T2.TEA_ID ");
            sql.Append(" where N.CON_ISSHOW!=-1 ");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoContests> list = GetList(table);
            return list;
        }

        public IList<BoContests> GetAllInnerContestsBy()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CON_ID,CON_COMPETITIONS,CON_CONTENTS,CON_YEAR,CON_MONTH,CON_ADDRESS,CON_TEACHERS,CON_NAME,CON_STARTTIME,CON_ENDTIME,CON_CREATOR,CON_EXPERIENCE,CON_IMPROVE,CON_SUMMARY,CON_ISCHECK,CON_ISSHOW ");
            sql.Append(" ,A.ACA_NAME AS ACA_NAME, T1.TEA_NAME AS TEA_NAME, T2.TEA_NAME AS MANAGER_NAME ");
            sql.Append(" ,M.COM_HOST, M.COM_UNDERTAKE, M.COM_LEVEL, M.COM_CAPITAL, M.COM_CONTENTS ");
            sql.Append(" ,(SELECT MIN(P.PRO_STARTTIME) FROM [ContestDB].[dbo].[Procedures] P ");
            sql.Append(" INNER JOIN  [ContestDB].[dbo].[Phases] H ON P.PRO_PHASES=H.PHA_ID ");
            sql.Append(" WHERE P.PRO_CONTESTS=N.CON_ID AND H.PHA_NAME LIKE '%报名%' ) ENROLL_TIME ");
            sql.Append(" FROM Contests AS N INNER JOIN Competitions AS M ON N.CON_COMPETITIONS=M.COM_ID ");
            sql.Append(" LEFT JOIN Academy AS A ON M.COM_ACADEMY=A.ACA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T1 ON N.CON_CREATOR=T1.TEA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T2 ON M.COM_MANAGER=T2.TEA_ID ");
            sql.Append(" WHERE M.COM_TYPE=0 ");

            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoContests> list = GetList(table);
            return list;
        }
        /// <summary>
        /// 得到分页的数据列表
        /// </summary>
        /// <param name="orderCon">排序条件</param>
        /// <param name="sqlCon">查询条件</param>
        /// <param name="pagesize">每页显示大小</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="records">返回查询到的记录</param>
        /// <returns>返回对象列表</returns>
        public IList<BoContests> GetPagerInnerContests(string sqlCon, string orderCon, int pagesize, int pageIndex, out int records)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select  * from v_Contest where CON_IsShow!=-1 ");
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append("and ");
                sql.Append(sqlCon);
            }
            DataTable table = dbhelper.ExecuteSp_Page(sql.ToString(), orderCon, pagesize, pageIndex, out records);
            IList<BoContests> list = GetVCon(table);
            return list != null && list.Count > 0 ? list : null;
        }
        public IList<BoContests> GetPagerInnerContests1(string academyId, string teacherId, int pagesize, int pageIndex, out int records)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select  * from v_Contest where 1=1 and CON_IsShow!=-1 ");
            if (!string.IsNullOrEmpty(academyId))
            {
                sql.Append("and ACA_ID='" + academyId + "' ");
            }

            if (!string.IsNullOrEmpty(teacherId))
            {
                sql.Append("and TEA_ID='" + teacherId + "' ");
            }

            DataTable table = dbhelper.ExecuteSp_Page(sql.ToString(), " CON_STARTTIME desc", pagesize, pageIndex, out records);
            IList<BoContests> list = GetVCon(table);
            return list != null && list.Count > 0 ? list : null;
        }
        public IList<BoContests> GetPagerInnerContests2(string academyId, string teacherId, int pagesize, int pageIndex, out int records)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select  * from v_Contest where 1=1 and CON_IsShow=2  ");
            if (!string.IsNullOrEmpty(academyId))
            {
                sql.Append("and ACA_ID='" + academyId + "' ");
            }

            if (!string.IsNullOrEmpty(teacherId))
            {
                sql.Append("and TEA_ID='" + teacherId + "' ");
            }

            DataTable table = dbhelper.ExecuteSp_Page(sql.ToString(), " CON_STARTTIME desc", pagesize, pageIndex, out records);
            IList<BoContests> list = GetVCon(table);
            return list != null && list.Count > 0 ? list : null;
        }
         /// <summary>
        /// 根据条件获取倩top行数据
        /// </summary>
        /// <param name="top">数据条数</param>
        /// <param name="sqlCon">条件</param>
        /// <param name="orderCon">排序条件</param>
        /// <returns>返回top行数据</returns>
        public IList<BoContests> getInnerContests(int top, string sqlCon, string orderCon)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select ");
            if (top > 0)
            {
                sql.Append("top ");
                sql.Append(top);
                sql.Append(" ");
            }
            sql.Append("* from v_Contest where 1=1 ");
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append("and ");
                sql.Append(sqlCon);
            }
            if (!string.IsNullOrEmpty(orderCon))
            {
                sql.Append(" order by ");
                sql.Append(orderCon);
            }
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoContests> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }

        public IList<BoContests> GetAllInnerContestsByAcademy(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CON_ID,CON_COMPETITIONS,CON_CONTENTS,CON_YEAR,CON_MONTH,CON_ADDRESS,CON_TEACHERS,CON_NAME,CON_STARTTIME,CON_ENDTIME,CON_CREATOR,CON_EXPERIENCE,CON_IMPROVE,CON_SUMMARY,CON_ISCHECK,CON_ISSHOW ");
            sql.Append(" ,A.ACA_NAME AS ACA_NAME, T1.TEA_NAME AS TEA_NAME, T2.TEA_NAME AS MANAGER_NAME ");
            sql.Append(" ,M.COM_HOST, M.COM_UNDERTAKE, M.COM_LEVEL, M.COM_CAPITAL, M.COM_CONTENTS ");
            sql.Append(" ,(SELECT MIN(P.PRO_STARTTIME) FROM [ContestDB].[dbo].[Procedures] P ");
            sql.Append(" INNER JOIN  [ContestDB].[dbo].[Phases] H ON P.PRO_PHASES=H.PHA_ID ");
            sql.Append(" WHERE P.PRO_CONTESTS=N.CON_ID AND H.PHA_NAME LIKE '%报名%' ) ENROLL_TIME ");
            sql.Append(" FROM Contests AS N INNER JOIN Competitions AS M ON N.CON_COMPETITIONS=M.COM_ID ");
            sql.Append(" LEFT JOIN Academy AS A ON M.COM_ACADEMY=A.ACA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T1 ON N.CON_CREATOR=T1.TEA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T2 ON M.COM_MANAGER=T2.TEA_ID ");
            sql.Append(" WHERE M.COM_TYPE=0 AND M.COM_ACADEMY=@COM_ACADEMY");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@COM_ACADEMY", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoContests> list = GetList(table);
            return list;
        }

        public IList<BoContests> GetAllOuterContests()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CON_ID,CON_COMPETITIONS,CON_CONTENTS,CON_YEAR,CON_MONTH,CON_ADDRESS,CON_TEACHERS,CON_NAME,CON_STARTTIME,CON_ENDTIME,CON_CREATOR,CON_EXPERIENCE,CON_IMPROVE,CON_SUMMARY,CON_ISCHECK,CON_ISSHOW ");
            sql.Append(" ,A.ACA_NAME AS ACA_NAME, T1.TEA_NAME AS TEA_NAME, T2.TEA_NAME AS MANAGER_NAME ");
            sql.Append(" ,M.COM_HOST, M.COM_UNDERTAKE, M.COM_LEVEL, M.COM_CAPITAL, M.COM_CONTENTS ");
            sql.Append(" ,(SELECT MIN(P.PRO_STARTTIME) FROM [ContestDB].[dbo].[Procedures] P ");
            sql.Append(" INNER JOIN  [ContestDB].[dbo].[Phases] H ON P.PRO_PHASES=H.PHA_ID ");
            sql.Append(" WHERE P.PRO_CONTESTS=N.CON_ID AND H.PHA_NAME LIKE '%报名%' ) ENROLL_TIME ");
            sql.Append(" FROM Contests AS N INNER JOIN Competitions AS M ON N.CON_COMPETITIONS=M.COM_ID ");
            sql.Append(" LEFT JOIN Academy AS A ON M.COM_ACADEMY=A.ACA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T1 ON N.CON_CREATOR=T1.TEA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T2 ON M.COM_MANAGER=T2.TEA_ID ");
            sql.Append(" WHERE M.COM_TYPE=1 and CON_IsShow!=-1");
           
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoContests> list = GetList(table);
            return list;
        }

        public IList<BoContests> GetAllOuterContestsByAcademy(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CON_ID,CON_COMPETITIONS,CON_CONTENTS,CON_YEAR,CON_MONTH,CON_ADDRESS,CON_TEACHERS,CON_NAME,CON_STARTTIME,CON_ENDTIME,CON_CREATOR,CON_EXPERIENCE,CON_IMPROVE,CON_SUMMARY,CON_ISCHECK,CON_ISSHOW ");
            sql.Append(" ,A.ACA_NAME AS ACA_NAME, T1.TEA_NAME AS TEA_NAME, T2.TEA_NAME AS MANAGER_NAME ");
            sql.Append(" ,M.COM_HOST, M.COM_UNDERTAKE, M.COM_LEVEL, M.COM_CAPITAL, M.COM_CONTENTS ");
            sql.Append(" ,(SELECT MIN(P.PRO_STARTTIME) FROM [ContestDB].[dbo].[Procedures] P ");
            sql.Append(" INNER JOIN  [ContestDB].[dbo].[Phases] H ON P.PRO_PHASES=H.PHA_ID ");
            sql.Append(" WHERE P.PRO_CONTESTS=N.CON_ID AND H.PHA_NAME LIKE '%报名%' ) ENROLL_TIME ");
            sql.Append(" FROM Contests AS N INNER JOIN Competitions AS M ON N.CON_COMPETITIONS=M.COM_ID ");
            sql.Append(" LEFT JOIN Academy AS A ON M.COM_ACADEMY=A.ACA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T1 ON N.CON_CREATOR=T1.TEA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T2 ON M.COM_MANAGER=T2.TEA_ID ");
            sql.Append(" WHERE M.COM_TYPE=1 AND M.COM_ACADEMY=@COM_ACADEMY ");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@COM_ACADEMY", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoContests> list = GetList(table);
            return list;
        }

        public IList<BoContests> GetContestsByAcademy(string academyId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CON_ID,CON_COMPETITIONS,CON_CONTENTS,CON_YEAR,CON_MONTH,CON_ADDRESS,CON_TEACHERS,CON_NAME,CON_STARTTIME,CON_ENDTIME,CON_CREATOR,CON_EXPERIENCE,CON_IMPROVE,CON_SUMMARY,CON_ISCHECK,CON_ISSHOW ");
            sql.Append(" ,A.ACA_NAME AS ACA_NAME, T1.TEA_NAME AS TEA_NAME, T2.TEA_NAME AS MANAGER_NAME ");
            sql.Append(" ,M.COM_HOST, M.COM_UNDERTAKE, M.COM_LEVEL, M.COM_CAPITAL, M.COM_CONTENTS ");
            sql.Append(" ,(SELECT MIN(P.PRO_STARTTIME) FROM [ContestDB].[dbo].[Procedures] P ");
            sql.Append(" INNER JOIN  [ContestDB].[dbo].[Phases] H ON P.PRO_PHASES=H.PHA_ID ");
            sql.Append(" WHERE P.PRO_CONTESTS=N.CON_ID AND H.PHA_NAME LIKE '%报名%' ) ENROLL_TIME ");
            sql.Append(" FROM Contests AS N INNER JOIN Competitions AS M ON N.CON_COMPETITIONS=M.COM_ID ");
            sql.Append(" LEFT JOIN Academy AS A ON M.COM_ACADEMY=A.ACA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T1 ON N.CON_CREATOR=T1.TEA_ID ");
            sql.Append(" LEFT JOIN Teachers AS T2 ON M.COM_MANAGER=T2.TEA_ID ");
            sql.Append("WHERE M.COM_ACADEMY=@COM_ACADEMY");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@COM_ACADEMY", SqlDbType.NVarChar, 50);
            parameter[0].Value = academyId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoContests> list = GetList(table);
            return list;
        }

        private List<BoContests> GetList(DataTable table)
        {
            List<BoContests> list = new List<BoContests>();
            if (table != null && table.Rows.Count > 0)
            {
                
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoContests model = new BoContests();
                    model.CON_ID = (string)table.Rows[i]["CON_ID"];
                    model.CON_COMPETITIONS = table.Rows[i]["CON_COMPETITIONS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_COMPETITIONS"];
                    model.CON_CONTENTS = table.Rows[i]["CON_CONTENTS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_CONTENTS"];
                    model.CON_YEAR = (Int32)table.Rows[i]["CON_YEAR"];
                    model.CON_MONTH = (Int32)table.Rows[i]["CON_MONTH"];
                    model.CON_ADDRESS = table.Rows[i]["CON_ADDRESS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_ADDRESS"];
                    model.CON_TEACHERS = table.Rows[i]["CON_TEACHERS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_TEACHERS"];
                    model.CON_NAME = (string)table.Rows[i]["CON_NAME"];
                    model.CON_STARTTIME = (DateTime)table.Rows[i]["CON_STARTTIME"];
                    model.CON_ENDTIME = (DateTime)table.Rows[i]["CON_ENDTIME"];
                    model.CON_CREATOR = table.Rows[i]["CON_CREATOR"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_CREATOR"];
                    model.CON_EXPERIENCE = table.Rows[i]["CON_EXPERIENCE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_EXPERIENCE"];
                    model.CON_IMPROVE = table.Rows[i]["CON_IMPROVE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_IMPROVE"];
                    model.CON_SUMMARY = table.Rows[i]["CON_SUMMARY"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_SUMMARY"];
                    model.CON_ISCHECK = (Int32)table.Rows[i]["CON_ISCHECK"];
                    model.CON_ISSHOW = (Int32)table.Rows[i]["CON_ISSHOW"];
                    model.ACA_NAME = table.Rows[i]["ACA_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["ACA_NAME"];
                    model.TEA_NAME = table.Rows[i]["TEA_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["TEA_NAME"];
                    model.MANAGER_NAME = table.Rows[i]["MANAGER_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MANAGER_NAME"];
                    model.COM_HOST = table.Rows[i]["COM_HOST"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_HOST"];
                    model.COM_UNDERTAKE = table.Rows[i]["COM_UNDERTAKE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_UNDERTAKE"];
                    model.COM_LEVEL = table.Rows[i]["COM_LEVEL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_LEVEL"];
                    model.COM_CAPITAL = table.Rows[i]["COM_CAPITAL"] == System.DBNull.Value ? 0 : (Int32)table.Rows[i]["COM_CAPITAL"];
                    model.COM_CONTENTS = table.Rows[i]["COM_CONTENTS"] == System.DBNull.Value ? "" : (String)table.Rows[i]["COM_CONTENTS"];
                    model.ENROLL_TIME = table.Rows[i]["ENROLL_TIME"] == System.DBNull.Value ? model.CON_STARTTIME : (DateTime)table.Rows[i]["ENROLL_TIME"];

                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoContests model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@CON_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@CON_COMPETITIONS",SqlDbType.NVarChar,50),
                
                new SqlParameter("@CON_YEAR",SqlDbType.Int,4),
                new SqlParameter("@CON_MONTH",SqlDbType.Int,4),
                new SqlParameter("@CON_ADDRESS",SqlDbType.NVarChar,50),
                new SqlParameter("@CON_TEACHERS",SqlDbType.NVarChar,50),
                new SqlParameter("@CON_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@CON_STARTTIME",SqlDbType.DateTime,8),
                new SqlParameter("@CON_ENDTIME",SqlDbType.DateTime,8),
                new SqlParameter("@CON_CREATOR",SqlDbType.NVarChar,50),
                new SqlParameter("@CON_EXPERIENCE",SqlDbType.Text,16),
                new SqlParameter("@CON_IMPROVE",SqlDbType.Text,16),
                new SqlParameter("@CON_SUMMARY",SqlDbType.Text,16),
                new SqlParameter("@CON_ISCHECK",SqlDbType.Int,4),
                new SqlParameter("@CON_ISSHOW",SqlDbType.Int,4),
                new SqlParameter("@CON_CONTENTS",SqlDbType.Text),
            };
            parameters[0].Value = model.CON_ID;
            parameters[1].Value = model.CON_COMPETITIONS;
            parameters[2].Value = model.CON_YEAR;
            parameters[3].Value = model.CON_MONTH;
            parameters[4].Value = model.CON_ADDRESS;
            parameters[5].Value = model.CON_TEACHERS;
            parameters[6].Value = model.CON_NAME;
            parameters[7].Value = model.CON_STARTTIME;
            parameters[8].Value = model.CON_ENDTIME;
            parameters[9].Value = model.CON_CREATOR;
            parameters[10].Value = model.CON_EXPERIENCE;
            parameters[11].Value = model.CON_IMPROVE;
            parameters[12].Value = model.CON_SUMMARY;
            parameters[13].Value = model.CON_ISCHECK;
            parameters[14].Value = model.CON_ISSHOW;
            parameters[15].Value = model.CON_CONTENTS;
            return parameters;
        }
        public IList<BoContests> GetContestByCompetition(string comId) 
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT CON_ID,CON_COMPETITIONS,CON_CONTENTS,CON_YEAR,CON_MONTH,CON_ADDRESS,CON_TEACHERS,CON_NAME,CON_STARTTIME,CON_ENDTIME,CON_CREATOR,CON_EXPERIENCE,CON_IMPROVE,CON_SUMMARY,CON_ISCHECK,CON_ISSHOW ");
            sql.Append(" ,M.COM_NAME AS COM_NAME FROM Contests AS C INNER JOIN Competitions AS M ON C.CON_COMPETITIONS=M.COM_ID ");
            sql.Append(" WHERE M.COM_NAME=@COM_NAME ");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@COM_NAME", SqlDbType.NVarChar, 50);
            parameter[0].Value = comId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoContests> list = GetList(table);
            return list;
        }

        /// <summary>
        /// 根据竞赛id得到竞赛视图
        /// </summary>
        /// <param name="conId">竞赛id</param>
        /// <returns>返回表格</returns>
        public BoContests GetV_Contest(string conId) 
        {
            string sql = "select * from v_Contest where CON_ID= @CON_ID AND CON_IsShow!=-1";
            SqlParameter[] parameter = new SqlParameter[1]
            {
                new SqlParameter("@CON_ID",SqlDbType.NVarChar,50)
            };
            parameter[0].Value = conId;
            DataTable table = dbhelper.ExcuteQuery(sql, parameter);
            IList<BoContests> list = GetVCon(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        private IList<BoContests> GetVCon(DataTable table)
        {
            List<BoContests> list = new List<BoContests>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoContests model = new BoContests();
                    model.CON_ID = (string)table.Rows[i]["CON_ID"];
                    model.CON_COMPETITIONS = table.Rows[i]["CON_COMPETITIONS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_COMPETITIONS"];
                    model.CON_CONTENTS = table.Rows[i]["CON_CONTENTS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_CONTENTS"];
                    model.CON_YEAR = (Int32)table.Rows[i]["CON_YEAR"];
                    model.CON_MONTH = (Int32)table.Rows[i]["CON_MONTH"];
                    model.CON_ADDRESS = table.Rows[i]["CON_ADDRESS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_ADDRESS"];
                    model.CON_TEACHERS = table.Rows[i]["CON_TEACHERS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_TEACHERS"];
                    model.CON_NAME = (string)table.Rows[i]["CON_NAME"];
                    model.CON_STARTTIME = (DateTime)table.Rows[i]["CON_STARTTIME"];
                    model.CON_ENDTIME = (DateTime)table.Rows[i]["CON_ENDTIME"];
                    model.CON_CREATOR = table.Rows[i]["CON_CREATOR"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_CREATOR"];
                    model.CON_EXPERIENCE = table.Rows[i]["CON_EXPERIENCE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_EXPERIENCE"];
                    model.CON_IMPROVE = table.Rows[i]["CON_IMPROVE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_IMPROVE"];
                    model.CON_SUMMARY = table.Rows[i]["CON_SUMMARY"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_SUMMARY"];
                    model.CON_ISCHECK = (Int32)table.Rows[i]["CON_ISCHECK"];
                    model.CON_ISSHOW = (Int32)table.Rows[i]["CON_ISSHOW"];
                    model.Competitions.COM_TYPE = table.Rows[i]["COM_TYPE"] == System.DBNull.Value ? -1 : (Int32)table.Rows[i]["COM_TYPE"];
                    model.Competitions.COM_NAME = table.Rows[i]["COM_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_NAME"];
                    model.Competitions.COM_ACADEMY = table.Rows[i]["COM_ACADEMY"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_ACADEMY"];
                    model.Competitions.COM_MANAGER = table.Rows[i]["COM_MANAGER"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_MANAGER"];
                    model.Competitions.COM_SEMESTER = table.Rows[i]["COM_SEMESTER"] == System.DBNull.Value ? -1 : (int)table.Rows[i]["COM_SEMESTER"];
                    model.Competitions.COM_FORM = table.Rows[i]["COM_FORM"] == System.DBNull.Value ? -1 : (int)table.Rows[i]["COM_FORM"];
                    model.Competitions.COM_SPECIALTY = table.Rows[i]["COM_SPECIALTY"] == System.DBNull.Value ? -1 : (int)table.Rows[i]["COM_SPECIALTY"];
                    model.Competitions.COM_HOST = table.Rows[i]["COM_HOST"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_HOST"];
                    model.Competitions.COM_UNDERTAKE = table.Rows[i]["COM_UNDERTAKE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_UNDERTAKE"];
                    model.Competitions.COM_LEVEL = table.Rows[i]["COM_LEVEL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_LEVEL"];
                    model.Competitions.COM_ISSHOW = bool.Parse(table.Rows[i]["COM_ISSHOW"].ToString());
                    model.Competitions.COM_CAPITAL = table.Rows[i]["COM_CAPITAL"] == System.DBNull.Value ? -1 : (int)table.Rows[i]["COM_CAPITAL"];
                    model.Competitions.COM_CONTENTS = table.Rows[i]["COM_CONTENTS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["COM_CONTENTS"];
                    model.ACA_NAME = table.Rows[i]["ACA_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["ACA_NAME"];
                    model.TEA_NAME = table.Rows[i]["TEA_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["TEA_NAME"];
                    model.MANAGER_NAME = table.Rows[i]["MANAGER_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["MANAGER_NAME"];
                    list.Add(model);
                }
            }
            return list;
        }
        /// <summary>
        /// 根据top数目及条件得到相应的数据
        /// </summary>
        /// <param name="top">数目</param>
        /// <param name="Constr">筛选条件</param>
        /// <param name="order">排序条件</param>
        /// <returns>返回数据表格</returns>
        public DataTable GetAllV_Contest(int top, string SqlStr, string order)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select ");
            if (top != 0)
                sql.Append("top " + top);
            sql.Append(" * from v_Contest where CON_ISCHECK='1' ");
            if (!string.IsNullOrEmpty(SqlStr))
                sql.Append("and " + SqlStr);
            if (!string.IsNullOrEmpty(order))
                sql.Append(" order by "+order);
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            return table;
        }
        /// <summary>
        /// 根据数据行数获取表格
        /// </summary>
        /// <param name="start">开始数据条数</param>
        /// <param name="end">结束数据集条数</param>
        /// <param name="sqlcon">查询条件</param>
        /// <returns>返回相应的表格</returns>
        public DataTable GetPage(string startNum, string endNum, string orderCon, string sqlCon)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from (select *,Row_Number() over ( order by ");
            sql.Append(orderCon);
            sql.Append(" )rownum from v_Contest where 1=1 ");
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append("and ");
                sql.Append(sqlCon);
            }
            sql.Append(" ) t where t.rownum>");
            sql.Append(startNum);
            sql.Append(" and t.rownum<=");
            sql.Append(endNum);
            return dbhelper.ExcuteQuery(sql.ToString());
        }

        /// <summary>
        /// 根据数据行数获取步骤表格
        /// </summary>
        /// <param name="start">开始数据条数</param>
        /// <param name="end">结束数据条数</param>
        /// <param name="orderCon">排序条件</param>
        /// <param name="sqlcon">查询条件</param>
        /// <returns>查询条件</returns>
        public DataTable GetParPage(string startNum, string endNum, string orderCon, string sqlCon)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT * from( select PRO_ID,PRO_CONTESTS,PRO_PHASES,PRO_STARTTIME,PRO_ENDTIME,PRO_ADDRESS,PRO_CONDUCTOR,PRO_CNRTITLE,PRO_METHOD,PRO_CONTENT,PHA_NAME,CON_NAME,Row_Number() over ( order by ");
            sql.Append(orderCon);
            sql.Append(" )rownum FROM Procedures P INNER JOIN Phases H ON P.PRO_PHASES=H.PHA_ID INNER JOIN Contests C ON P.PRO_CONTESTS=C.CON_ID ");
            if (!string.IsNullOrEmpty(sqlCon))
            {
                sql.Append("and ");
                sql.Append(sqlCon);
            }
            sql.Append(" ) t where t.rownum>");
            sql.Append(startNum);
            sql.Append(" and t.rownum<=");
            sql.Append(endNum);
            return dbhelper.ExcuteQuery(sql.ToString());
        }
    }
}